package zjs.dc.controller;

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;

/**
 * @Class:
 * @Author: guoml
 * @Description: 针对企查查t_ipo_company_balance_sheet表和中数t_comp_fin_probalsheetnew表进行数据映射开发
 * @Date: 2021/12/7 12:51
 */

public class TIpoCompanyBalanceSheet {
    public static void main(String[] args) {
        SparkSession spark = SparkSession.builder().appName("t_ipo_company_balance_sheet").enableHiveSupport().getOrCreate();
        spark.sparkContext().setLogLevel("ERROR");
        //获取中数增量数据表中的新增数据（left join）
        Dataset<Row> insert_data = spark.sql("SELECT md5(concat(100,a.ID)) as ID,\n" +
                "       b.SecuCode,\n" +
                " to_json(array(named_struct('CashEquivalents',CashEquivalents,\n" +
                "'Cash',Cash,\n" +
                "'ClientDeposit',ClientDeposit,\n" +
                "'DepositInCentralBank',DepositInCentralBank,\n" +
                "'TradingAssets',TradingAssets,\n" +
                "'AmongTradingAssets',AmongTradingAssets,\n" +
                "'AmongFinAetAtFValTPL',AmongFinAetAtFValTPL,\n" +
                "'BillAccReceivable',BillAccReceivable,\n" +
                "'BillReceivable',BillReceivable,\n" +
                "'AccountReceivable',AccountReceivable,\n" +
                "'OtherReceivableED',OtherReceivableED,\n" +
                "'OtherReceivable',OtherReceivable,\n" +
                "'DividendReceivable',DividendReceivable,\n" +
                "'InterestReceivable',InterestReceivable,\n" +
                "'AdvancePayment',AdvancePayment,\n" +
                "'Inventories',Inventories,\n" +
                "'BearerBiologicalAssets',BearerBiologicalAssets,\n" +
                "'DeferredExpense',DeferredExpense,\n" +
                "'NonCurrentAssetIn1Year',NonCurrentAssetIn1Year,\n" +
                "'OtherCurrentAssets',OtherCurrentAssets,\n" +
                "'CAExceptionalItems',CAExceptionalItems,\n" +
                "'CAAdjustmentItems',CAAdjustmentItems,\n" +
                "'TotalCurrentAssets',TotalCurrentAssets,\n" +
                "'HoldForSaleAssets',HoldForSaleAssets,\n" +
                "'HoldToMaturityInvestments',HoldToMaturityInvestments,\n" +
                "'InvestmentProperty',InvestmentProperty,\n" +
                "'LongtermEquityInvest',LongtermEquityInvest,\n" +
                "'LongtermReceivableAccount',LongtermReceivableAccount,\n" +
                "'TConstruInProcess',TConstruInProcess,\n" +
                "'ConstruInProcess',ConstruInProcess,\n" +
                "'ConstructionMaterials',ConstructionMaterials,\n" +
                "'TotalFixedAsset',TotalFixedAsset,\n" +
                "'FixedAssets',FixedAssets,\n" +
                "'FixedAssetsLiquidation',FixedAssetsLiquidation,\n" +
                "'BiologicalAssets',BiologicalAssets,\n" +
                "'OilGasAssets',OilGasAssets,\n" +
                "'IntangibleAssets',IntangibleAssets,\n" +
                "'SeatCosts',SeatCosts,\n" +
                "'DevelopmentExpenditure',DevelopmentExpenditure,\n" +
                "'GoodWill',GoodWill,\n" +
                "'LongDeferredExpense',LongDeferredExpense,\n" +
                "'DeferredTaxAssets',DeferredTaxAssets,\n" +
                "'OtherNonCurrentAssets',OtherNonCurrentAssets,\n" +
                "'NCAExceptionalItems',NCAExceptionalItems,\n" +
                "'NCAAdjustmentItems',NCAAdjustmentItems,\n" +
                "'TotalNonCurrentAssets',TotalNonCurrentAssets,\n" +
                "'LoanAndAccountReceivables',LoanAndAccountReceivables,\n" +
                "'SettlementProvi',SettlementProvi,\n" +
                "'ClientProvi',ClientProvi,\n" +
                "'DepositInInterbank',DepositInInterbank,\n" +
                "'RMetal',RMetal,\n" +
                "'LendCapital',LendCapital,\n" +
                "'DerivativeAssets',DerivativeAssets,\n" +
                "'BoughtSellbackAssets',BoughtSellbackAssets,\n" +
                "'LoanAndAdvance',LoanAndAdvance,\n" +
                "'InsuranceReceivables',InsuranceReceivables,\n" +
                "'ReceivableSubrogationFee',ReceivableSubrogationFee,\n" +
                "'ReinsuranceReceivables',ReinsuranceReceivables,\n" +
                "'ResReiReceContracts',ResReiReceContracts,\n" +
                "'ReceivableUnearnedR',ReceivableUnearnedR,\n" +
                "'ReceivableClaimsR',ReceivableClaimsR,\n" +
                "'ReceivableLifeR',ReceivableLifeR,\n" +
                "'ReceivableLTHealthR',ReceivableLTHealthR,\n" +
                "'InsurerImpawnLoan',InsurerImpawnLoan,\n" +
                "'FixedDeposit',FixedDeposit,\n" +
                "'RefundableDeposit',RefundableDeposit,\n" +
                "'RefundableCapitalDeposit',RefundableCapitalDeposit,\n" +
                "'IndependenceAccountAssets',IndependenceAccountAssets,\n" +
                "'OtherAssets',OtherAssets,\n" +
                "'AExceptionalItems',AExceptionalItems,\n" +
                "'AAdjustmentItems',AAdjustmentItems,\n" +
                "'TotalAssets',TotalAssets,\n" +
                "'ShortTermLoan',ShortTermLoan,\n" +
                "'ImpawnedLoan',ImpawnedLoan,\n" +
                "'NotAccountsPayable',NotAccountsPayable,\n" +
                "'NotesPayable',NotesPayable,\n" +
                "'AccountsPayable',AccountsPayable,\n" +
                "'STBondsPayable',STBondsPayable,\n" +
                "'AdvanceReceipts',AdvanceReceipts,\n" +
                "'SalariesPayable',SalariesPayable,\n" +
                "'TaxsPayable',TaxsPayable,\n" +
                "'AccruedExpense',AccruedExpense,\n" +
                "'DeferredProceeds',DeferredProceeds,\n" +
                "'NonCurrentLiabilityIn1Year',NonCurrentLiabilityIn1Year,\n" +
                "'OtherCurrentLiability',OtherCurrentLiability,\n" +
                "'CLExceptionalItems',CLExceptionalItems,\n" +
                "'CLAdjustmentItems',CLAdjustmentItems,\n" +
                "'TotalCurrentLiability',TotalCurrentLiability,\n" +
                "'LongtermLoan',LongtermLoan,\n" +
                "'LTAccountPayableTotal',LTAccountPayableTotal,\n" +
                "'LongtermAccountPayable',LongtermAccountPayable,\n" +
                "'SpecificAccountPayable',SpecificAccountPayable,\n" +
                "'EstimateLiability',EstimateLiability,\n" +
                "'DeferredTaxLiability',DeferredTaxLiability,\n" +
                "'OtherNonCurrentLiability',OtherNonCurrentLiability,\n" +
                "'NCLExceptionalItems',NCLExceptionalItems,\n" +
                "'NCLAdjustmentItems',NCLAdjustmentItems,\n" +
                "'TotalNonCurrentLiability',TotalNonCurrentLiability,\n" +
                "'BorrowingFromCentralBank',BorrowingFromCentralBank,\n" +
                "'BorrowingCapital',BorrowingCapital,\n" +
                "'DerivativeLiability',DerivativeLiability,\n" +
                "'SoldBuybackSecuProceeds',SoldBuybackSecuProceeds,\n" +
                "'AbsInterDeposits',AbsInterDeposits,\n" +
                "'Deposit',Deposit,\n" +
                "'DepositOfInterbank',DepositOfInterbank,\n" +
                "'ProxySecuProceeds',ProxySecuProceeds,\n" +
                "'SubIssueSecuProceeds',SubIssueSecuProceeds,\n" +
                "'DepositsReceived',DepositsReceived,\n" +
                "'AdvanceInsurance',AdvanceInsurance,\n" +
                "'CommissionPayable',CommissionPayable,\n" +
                "'ReinsurancePayables',ReinsurancePayables,\n" +
                "'CompensationPayable',CompensationPayable,\n" +
                "'PolicyDividendPayable',PolicyDividendPayable,\n" +
                "'InsurerDepositInvestment',InsurerDepositInvestment,\n" +
                "'LTInsContractReserve',LTInsContractReserve,\n" +
                "'UnearnedPremiumReserve',UnearnedPremiumReserve,\n" +
                "'OutstandingClaimReserve',OutstandingClaimReserve,\n" +
                "'LifeInsuranceReserve',LifeInsuranceReserve,\n" +
                "'LTHealthInsuranceLR',LTHealthInsuranceLR,\n" +
                "'IndependenceLiability',IndependenceLiability,\n" +
                "'OtherLiability',OtherLiability,\n" +
                "'LExceptionalItems',LExceptionalItems,\n" +
                "'LAdjustmentItems',LAdjustmentItems,\n" +
                "'TotalLiability',TotalLiability,\n" +
                "'PaidInCapital',PaidInCapital,\n" +
                "'CapitalReserveFund',CapitalReserveFund,\n" +
                "'TreasuryStock',TreasuryStock,\n" +
                "'SurplusReserveFund',SurplusReserveFund,\n" +
                "'RetainedProfit',RetainedProfit,\n" +
                "'OrdinaryRiskReserveFund',OrdinaryRiskReserveFund,\n" +
                "'ForeignCurrencyReportConvDiff',ForeignCurrencyReportConvDiff,\n" +
                "'UncertainedInvestmentLoss',UncertainedInvestmentLoss,\n" +
                "'OtherReserves',OtherReserves,\n" +
                "'SpecificReserves',SpecificReserves,\n" +
                "'SEExceptionalItems',SEExceptionalItems,\n" +
                "'SEAdjustmentItems',SEAdjustmentItems,\n" +
                "'SEWithoutMI',SEWithoutMI,\n" +
                "'MinorityInterests',MinorityInterests,\n" +
                "'OtherItemsEffectingSE',OtherItemsEffectingSE,\n" +
                "'TotalShareholderEquity',TotalShareholderEquity,\n" +
                "'LEExceptionalItems',LEExceptionalItems,\n" +
                "'LEAdjustmentItems',LEAdjustmentItems,\n" +
                "'TotalLiabilityAndEquity',TotalLiabilityAndEquity,\n" +
                "'BondsPayable',BondsPayable,\n" +
                "'LPreferStock',LPreferStock,\n" +
                "'LPerpetualDebt',LPerpetualDebt,\n" +
                "'OtherEquityinstruments',OtherEquityinstruments,\n" +
                "'EPreferStock',EPreferStock,\n" +
                "'EPerpetualDebt',EPerpetualDebt,\n" +
                "'OtherCompositeIncome',OtherCompositeIncome,\n" +
                "'HoldAndFSAssets',HoldAndFSAssets,\n" +
                "'HoldAndFSLi',HoldAndFSLi,\n" +
                "'LongSalariesPay',LongSalariesPay,\n" +
                "'LongDeferIncome',LongDeferIncome,\n" +
                "'ContractualAssets',ContractualAssets,\n" +
                "'OthNonCurFinAssets',OthNonCurFinAssets,\n" +
                "'ContractLiability',ContractLiability,\n" +
                "'TradeRiskRSRVFd',TradeRiskRSRVFd,\n" +
                "'OtherPayableED',OtherPayableED,\n" +
                "'OtherPayable',OtherPayable,\n" +
                "'DividendPayable',DividendPayable,\n" +
                "'InterestPayable',InterestPayable,\n" +
                "'ReceivablesFin',ReceivablesFin,\n" +
                "'Financing',Financing,\n" +
                "'FinLeaseReceivable',FinLeaseReceivable,\n" +
                "'UsufructAssets',UsufructAssets,\n" +
                "'InsContractReserve',InsContractReserve,\n" +
                "'LeaseLiabilities',LeaseLiabilities,\n" +
                "'FinLeasesPayable',FinLeasesPayable,\n" +
                "'DepositInAssociate',DepositInAssociate,\n" +
                "'DebtAssets',DebtAssets,\n" +
                "'DebitofAssociate',DebitofAssociate,\n" +
                "'Receivables',Receivables,\n" +
                "'CashDepositReceive',CashDepositReceive,\n" +
                "'PledgDepositReceive',PledgDepositReceive,\n" +
                "'SettlementReceive',SettlementReceive,\n" +
                "'RiskOfLossReceive',RiskOfLossReceive,\n" +
                "'FeeCommissionReceive',FeeCommissionReceive,\n" +
                "'DebtInvestment',DebtInvestment,\n" +
                "'AmongDebtInvestment',AmongDebtInvestment,\n" +
                "'AmongFinAetAtAmorCost',AmongFinAetAtAmorCost,\n" +
                "'OthDebtInvestment',OthDebtInvestment,\n" +
                "'AmongOthDebtInvest',AmongOthDebtInvest,\n" +
                "'AmongFinAetAtFValTOCI',AmongFinAetAtFValTOCI,\n" +
                "'OthEquityInstrument',OthEquityInstrument,\n" +
                "'AmongOthEquInstrument',AmongOthEquInstrument,\n" +
                "'AmongEquInsAtFValTOCI',AmongEquInsAtFValTOCI,\n" +
                "'FutureMemberInvestment',FutureMemberInvestment,\n" +
                "'FinancialInvestment',FinancialInvestment,\n" +
                "'ShortTermBondPay',ShortTermBondPay,\n" +
                "'TradingLiability',TradingLiability,\n" +
                "'AmongTradingLiability',AmongTradingLiability,\n" +
                "'AmongFinLiaAtFValTPL',AmongFinLiaAtFValTPL,\n" +
                "'Payables',Payables,\n" +
                "'CashDepositPay',CashDepositPay,\n" +
                "'PledgDepositPay',PledgDepositPay,\n" +
                "'FutureProtectFundPay',FutureProtectFundPay,\n" +
                "'GuarantCompensateRSRV',GuarantCompensateRSRV,\n" +
                "'GuaranteeReserve',GuaranteeReserve,\n" +
                "'FutureRiskReserve',FutureRiskReserve))) as report_json,\n" +
                "       '' as created_date,\n" +
                "       '' as update_date,\n" +
                "       a.EndDate,\n" +
                "       '' as report_encrypt_key,\n" +
                "       regexp_replace(current_date(),'-',''),\n" +
                "       '1' as isadd\n" +
                "FROM\n" +
                "  (select a.* from (select a.*,row_number ( ) over ( PARTITION BY CompanyCode,EndDate ORDER BY InfoPublDate desc,IfAdjusted) AS adjust from a_dc_ep_ods.LC_BalanceSheetAll a where IfMerged = 1 and IfComplete = 1 and InfoSourceCode in (110101,110102,110103,110104))a where a.adjust = 1)a\n" +
                "INNER JOIN\n" +
                "  (select * from a_dc_ep_ods.SecuMain where ListedState = 1 and ListedSector not in (3,7) and SecuCategory = 1) b on a.CompanyCode = b.CompanyCode").union(spark.sql("SELECT md5(concat(200,a.ID)) as ID,\n" +
                "       b.SecuCode,\n" +
                "to_json(array(named_struct('CashEquivalents',CashEquivalents,\n" +
                "'ClientDeposit',ClientDeposit,\n" +
                "'TradingAssets',TradingAssets,\n" +
                "'BillAccReceivable',BillAccReceivable,\n" +
                "'BillReceivable',BillReceivable,\n" +
                "'AR',AR,\n" +
                "'OtherReceivableED',OtherReceivableED,\n" +
                "'DivReceivable',DivReceivable,\n" +
                "'INTReceivable',INTReceivable,\n" +
                "'OtherReceivable',OtherReceivable,\n" +
                "'AdvancePayment',AdvancePayment,\n" +
                "'Inventories',Inventories,\n" +
                "'BearerBioAssets',BearerBioAssets,\n" +
                "'DeferredExpense',DeferredExpense,\n" +
                "'NonCAInOneYr',NonCAInOneYr,\n" +
                "'OtherCA',OtherCA,\n" +
                "'CASepcialItem',CASepcialItem,\n" +
                "'CAAdjustItems',CAAdjustItems,\n" +
                "'TotalCA',TotalCA,\n" +
                "'HFSAssets',HFSAssets,\n" +
                "'HoldToMatInvest',HoldToMatInvest,\n" +
                "'InvestProperty',InvestProperty,\n" +
                "'LTEquityInvest',LTEquityInvest,\n" +
                "'LTRECVAccount',LTRECVAccount,\n" +
                "'FixedAssets',FixedAssets,\n" +
                "'ConstruMaterial',ConstruMaterial,\n" +
                "'ConstruInPROC',ConstruInPROC,\n" +
                "'FALiquidation',FALiquidation,\n" +
                "'BiologicalAsset',BiologicalAsset,\n" +
                "'OilGasAssets',OilGasAssets,\n" +
                "'IntangibleAsset',IntangibleAsset,\n" +
                "'SeatCosts',SeatCosts,\n" +
                "'DevelopExpend',DevelopExpend,\n" +
                "'GoodWill',GoodWill,\n" +
                "'LongDeferExps',LongDeferExps,\n" +
                "'DeferTaxAssets',DeferTaxAssets,\n" +
                "'OtherNonCA',OtherNonCA,\n" +
                "'NCASpecialItems',NCASpecialItems,\n" +
                "'NCAAdjustItems',NCAAdjustItems,\n" +
                "'TotalNonCA',TotalNonCA,\n" +
                "'SettlementProvi',SettlementProvi,\n" +
                "'ClientProvi',ClientProvi,\n" +
                "'DPSTInInterbank',DPSTInInterbank,\n" +
                "'RMetal',RMetal,\n" +
                "'LendCapital',LendCapital,\n" +
                "'DerivativeAsset',DerivativeAsset,\n" +
                "'BuySBAsset',BuySBAsset,\n" +
                "'LoanAndAdvance',LoanAndAdvance,\n" +
                "'InsurReceivable',InsurReceivable,\n" +
                "'RECVSubrogFee',RECVSubrogFee,\n" +
                "'ReinsurRECV',ReinsurRECV,\n" +
                "'RECVUnearnedR',RECVUnearnedR,\n" +
                "'RECVClaimsR',RECVClaimsR,\n" +
                "'ReceivableLifeR',ReceivableLifeR,\n" +
                "'RECVLTHealthR',RECVLTHealthR,\n" +
                "'InsurerImpawnLN',InsurerImpawnLN,\n" +
                "'FixedDeposit',FixedDeposit,\n" +
                "'RefundableDPST',RefundableDPST,\n" +
                "'IndependAcAsset',IndependAcAsset,\n" +
                "'OtherAssets',OtherAssets,\n" +
                "'ASpecialItems',ASpecialItems,\n" +
                "'AAdjustItems',AAdjustItems,\n" +
                "'TotalAssets',TotalAssets,\n" +
                "'ShortTermLoan',ShortTermLoan,\n" +
                "'ImpawnedLoan',ImpawnedLoan,\n" +
                "'TradeLiability',TradeLiability,\n" +
                "'NotAccountsPayable',NotAccountsPayable,\n" +
                "'NotesPayable',NotesPayable,\n" +
                "'AccountsPayable',AccountsPayable,\n" +
                "'AdvanceReceipts',AdvanceReceipts,\n" +
                "'SalariesPayable',SalariesPayable,\n" +
                "'OtherPayableED',OtherPayableED,\n" +
                "'DividendPayable',DividendPayable,\n" +
                "'InterestPayable',InterestPayable,\n" +
                "'OtherPayable',OtherPayable,\n" +
                "'TaxsPayable',TaxsPayable,\n" +
                "'AccruedExpense',AccruedExpense,\n" +
                "'EstimateLi',EstimateLi,\n" +
                "'DeferProceeds',DeferProceeds,\n" +
                "'NonCurLiInOneYr',NonCurLiInOneYr,\n" +
                "'OtherCurLi',OtherCurLi,\n" +
                "'CLSpecialItems',CLSpecialItems,\n" +
                "'CLAdjustItems',CLAdjustItems,\n" +
                "'TotalCurrentLi',TotalCurrentLi,\n" +
                "'LongtermLoan',LongtermLoan,\n" +
                "'LongtermAP',LongtermAP,\n" +
                "'SpecificAP',SpecificAP,\n" +
                "'DeferTaxLi',DeferTaxLi,\n" +
                "'OtherNonCurLi',OtherNonCurLi,\n" +
                "'NCLSpecialItems',NCLSpecialItems,\n" +
                "'NCLAdjustItems',NCLAdjustItems,\n" +
                "'TotalNonCurLi',TotalNonCurLi,\n" +
                "'BorrowFromCB',BorrowFromCB,\n" +
                "'DPSTOfInterbank',DPSTOfInterbank,\n" +
                "'BorrowingCap',BorrowingCap,\n" +
                "'DerivativeLi',DerivativeLi,\n" +
                "'SoldBuybackSec',SoldBuybackSec,\n" +
                "'Deposit',Deposit,\n" +
                "'ProxySecProceed',ProxySecProceed,\n" +
                "'SuIssSecProceed',SuIssSecProceed,\n" +
                "'AdvanceInsur',AdvanceInsur,\n" +
                "'CommPayable',CommPayable,\n" +
                "'ReinsurPayable',ReinsurPayable,\n" +
                "'CompensPayable',CompensPayable,\n" +
                "'PLCYDivPayable',PLCYDivPayable,\n" +
                "'InsurDPSTInvest',InsurDPSTInvest,\n" +
                "'UPR',UPR,\n" +
                "'OsClaimRSRV',OsClaimRSRV,\n" +
                "'LifeInsurRSRV',LifeInsurRSRV,\n" +
                "'LTHealthInsurLR',LTHealthInsurLR,\n" +
                "'IndependLi',IndependLi,\n" +
                "'OtherLiability',OtherLiability,\n" +
                "'LSpecialItems',LSpecialItems,\n" +
                "'LAdjustItems',LAdjustItems,\n" +
                "'TotalLi',TotalLi,\n" +
                "'PaidInCapital',PaidInCapital,\n" +
                "'CapitalRSRVFund',CapitalRSRVFund,\n" +
                "'SurplusRSRVFund',SurplusRSRVFund,\n" +
                "'RetainedProfit',RetainedProfit,\n" +
                "'TreasuryStock',TreasuryStock,\n" +
                "'OrdRiskRSRVFund',OrdRiskRSRVFund,\n" +
                "'FGNCyRPTConDiff',FGNCyRPTConDiff,\n" +
                "'MinorInterests',MinorInterests,\n" +
                "'OtherItemEffSE',OtherItemEffSE,\n" +
                "'SESpecialItems',SESpecialItems,\n" +
                "'SEAdjustItems',SEAdjustItems,\n" +
                "'TotalSE',TotalSE,\n" +
                "'LESpecialItems',LESpecialItems,\n" +
                "'LEAdjustItems',LEAdjustItems,\n" +
                "'TOTLiAndEquity',TOTLiAndEquity,\n" +
                "'SEWithoutMI',SEWithoutMI,\n" +
                "'LoanAndAcctRECV',LoanAndAcctRECV,\n" +
                "'RefundCapDPST',RefundCapDPST,\n" +
                "'STBondsPayable',STBondsPayable,\n" +
                "'DepositsRECD',DepositsRECD,\n" +
                "'UncerInvestLoss',UncerInvestLoss,\n" +
                "'OtherReserves',OtherReserves,\n" +
                "'SpecificRSRV',SpecificRSRV,\n" +
                "'TradeRiskRSRVFd',TradeRiskRSRVFd,\n" +
                "'BondsPayable',BondsPayable,\n" +
                "'LPreferStock',LPreferStock,\n" +
                "'LPerpetualDebt',LPerpetualDebt,\n" +
                "'OtherEquityinst',OtherEquityinst,\n" +
                "'EPreferStock',EPreferStock,\n" +
                "'EPerpetualDebt',EPerpetualDebt,\n" +
                "'OtherCOMPIncome',OtherCOMPIncome,\n" +
                "'HoldAndFSAssets',HoldAndFSAssets,\n" +
                "'HoldAndFSLi',HoldAndFSLi,\n" +
                "'LongSalariesPay',LongSalariesPay,\n" +
                "'LongDeferIncome',LongDeferIncome,\n" +
                "'FuturesDeposits',FuturesDeposits,\n" +
                "'CashDepositReceive',CashDepositReceive,\n" +
                "'PledgDepositReceive',PledgDepositReceive,\n" +
                "'SettlementReceive',SettlementReceive,\n" +
                "'RiskOfLossReceive',RiskOfLossReceive,\n" +
                "'CommissionReceive',CommissionReceive,\n" +
                "'SubContractReceive',SubContractReceive,\n" +
                "'FutureMemberInvestment',FutureMemberInvestment,\n" +
                "'SecurityClearReceive',SecurityClearReceive,\n" +
                "'PrepaidCommission',PrepaidCommission,\n" +
                "'CreditRightInvestment',CreditRightInvestment,\n" +
                "'PaidReparations',PaidReparations,\n" +
                "'PaymentInAdvance',PaymentInAdvance,\n" +
                "'FundsLending',FundsLending,\n" +
                "'PostLossGoods',PostLossGoods,\n" +
                "'CashDepositPay',CashDepositPay,\n" +
                "'PledgDepositPay',PledgDepositPay,\n" +
                "'FutureRiskReserve',FutureRiskReserve,\n" +
                "'FutureProtectFundPay',FutureProtectFundPay,\n" +
                "'CustomerInterbankDep',CustomerInterbankDep,\n" +
                "'InsuranceContractReser',InsuranceContractReser,\n" +
                "'InsurancePay',InsurancePay,\n" +
                "'SecurityClearPay',SecurityClearPay,\n" +
                "'InsuranceProtectFund',InsuranceProtectFund,\n" +
                "'ShortTermBondPay',ShortTermBondPay,\n" +
                "'MarginTradeSecurity',MarginTradeSecurity,\n" +
                "'AgencyAsset',AgencyAsset,\n" +
                "'AgencyLiability',AgencyLiability,\n" +
                "'InsurRSRV',InsurRSRV,\n" +
                "'CompensatePayment',CompensatePayment,\n" +
                "'GuarantCompensateRSRV',GuarantCompensateRSRV,\n" +
                "'ReceivablesFin',ReceivablesFin,\n" +
                "'MandateLoans',MandateLoans,\n" +
                "'ContractualAssets',ContractualAssets,\n" +
                "'DebtPayAssets',DebtPayAssets,\n" +
                "'DebtInvestment',DebtInvestment,\n" +
                "'UsufructAssets',UsufructAssets,\n" +
                "'OthNonCurFinAssets',OthNonCurFinAssets,\n" +
                "'OthEquityInstrument',OthEquityInstrument,\n" +
                "'OthDebtInvestment',OthDebtInvestment,\n" +
                "'ContractLiability',ContractLiability,\n" +
                "'GuaranteeReser',GuaranteeReser))) as report_json,\n" +
                "       '' as created_date,\n" +
                "       '' as update_date,\n" +
                "       a.EndDate,\n" +
                "       '' as report_encrypt_key,\n" +
                "       regexp_replace(current_date(),'-',''),\n" +
                "       '1' as isadd\n" +
                "FROM\n" +
                "  (select a.* from (select a.*,row_number ( ) over ( PARTITION BY CompanyCode,EndDate ORDER BY InfoPublDate desc,IfAdjusted) AS adjust from a_dc_ep_ods.NQ_BalanceSheet a where IfMerged = 1 and IfComplete = 1 and InfoSource in (2,3,4,5))a where a.adjust = 1)a\n" +
                "INNER JOIN\n" +
                "  (select * from a_dc_ep_ods.SecuMain where ListedState = 1 and ListedSector =3 and SecuCategory = 1) b on a.CompanyCode = b.CompanyCode")).union(spark.sql("SELECT md5(concat(300,a.ID)) as ID,\n" +
                "       b.SecuCode,\n" +
                "to_json(array(named_struct('CashEquivalents',CashEquivalents,\n" +
                "'Cash',Cash,\n" +
                "'ClientDeposit',ClientDeposit,\n" +
                "'DepositInCentralBank',DepositInCentralBank,\n" +
                "'TradingAssets',TradingAssets,\n" +
                "'AmongTradingAssets',AmongTradingAssets,\n" +
                "'AmongFinAetAtFValTPL',AmongFinAetAtFValTPL,\n" +
                "'BillAccReceivable',BillAccReceivable,\n" +
                "'BillReceivable',BillReceivable,\n" +
                "'AccountReceivable',AccountReceivable,\n" +
                "'OtherReceivableED',OtherReceivableED,\n" +
                "'OtherReceivables',OtherReceivables,\n" +
                "'DividendReceivables',DividendReceivables,\n" +
                "'InterestReceivables',InterestReceivables,\n" +
                "'AdvancePayment',AdvancePayment,\n" +
                "'Inventories',Inventories,\n" +
                "'BearerBiologicalAssets',BearerBiologicalAssets,\n" +
                "'ContractualAssets',ContractualAssets,\n" +
                "'DeferredExpense',DeferredExpense,\n" +
                "'HoldAndFSAssets',HoldAndFSAssets,\n" +
                "'NonCurAssetInOYear',NonCurAssetInOYear,\n" +
                "'OtherCurrentAssets',OtherCurrentAssets,\n" +
                "'CAExceptionalItems',CAExceptionalItems,\n" +
                "'CAAdjustmentItems',CAAdjustmentItems,\n" +
                "'TotalCurrentAssets',TotalCurrentAssets,\n" +
                "'DebtInvestment',DebtInvestment,\n" +
                "'AmongDebtInvestment',AmongDebtInvestment,\n" +
                "'AmongFinAetAtAmorCost',AmongFinAetAtAmorCost,\n" +
                "'OthDebtInvestment',OthDebtInvestment,\n" +
                "'AmongOthDebtInvest',AmongOthDebtInvest,\n" +
                "'AmongFinAetAtFValTOCI',AmongFinAetAtFValTOCI,\n" +
                "'HoldForSaleAssets',HoldForSaleAssets,\n" +
                "'HoldToMaturInv',HoldToMaturInv,\n" +
                "'OthEquityInstrument',OthEquityInstrument,\n" +
                "'AmongOthEquInstrument',AmongOthEquInstrument,\n" +
                "'AmongEquInsAtFValTOCI',AmongEquInsAtFValTOCI,\n" +
                "'OthNonCurFinAssets',OthNonCurFinAssets,\n" +
                "'InvestmentProperty',InvestmentProperty,\n" +
                "'LongTermEquityInvest',LongTermEquityInvest,\n" +
                "'LtermReceAcc',LtermReceAcc,\n" +
                "'TotalFixedAsset',TotalFixedAsset,\n" +
                "'FixedAssets',FixedAssets,\n" +
                "'FixedAssetsLiquidation',FixedAssetsLiquidation,\n" +
                "'TConstruInProcess',TConstruInProcess,\n" +
                "'ConstruInProcess',ConstruInProcess,\n" +
                "'ConstructionMaterials',ConstructionMaterials,\n" +
                "'BiologicalAssets',BiologicalAssets,\n" +
                "'OilGasAssets',OilGasAssets,\n" +
                "'IntangibleAssets',IntangibleAssets,\n" +
                "'SeatCosts',SeatCosts,\n" +
                "'DevelopmentExpenditure',DevelopmentExpenditure,\n" +
                "'GoodWill',GoodWill,\n" +
                "'LongDeferredExpense',LongDeferredExpense,\n" +
                "'DeferredTaxAssets',DeferredTaxAssets,\n" +
                "'OtherNonCurrentAssets',OtherNonCurrentAssets,\n" +
                "'NCAExceptionalItems',NCAExceptionalItems,\n" +
                "'NCAAdjustmentItems',NCAAdjustmentItems,\n" +
                "'TotalNonCurrentAssets',TotalNonCurrentAssets,\n" +
                "'LoanAndAccRec',LoanAndAccRec,\n" +
                "'SettlementProvi',SettlementProvi,\n" +
                "'ClientProvi',ClientProvi,\n" +
                "'DepositInInterbank',DepositInInterbank,\n" +
                "'RMetal',RMetal,\n" +
                "'LendCapital',LendCapital,\n" +
                "'DerivativeAssets',DerivativeAssets,\n" +
                "'BoughtSellbackAssets',BoughtSellbackAssets,\n" +
                "'LoanAndAdvance',LoanAndAdvance,\n" +
                "'InsuranceReceivables',InsuranceReceivables,\n" +
                "'ReceSubFee',ReceSubFee,\n" +
                "'ReinsuranceReceivables',ReinsuranceReceivables,\n" +
                "'ResReiReceContracts',ResReiReceContracts,\n" +
                "'ReceivableUnearnedR',ReceivableUnearnedR,\n" +
                "'ReceivableClaimsR',ReceivableClaimsR,\n" +
                "'ReceivableLifeR',ReceivableLifeR,\n" +
                "'ReceivableLTHealthR',ReceivableLTHealthR,\n" +
                "'InsurerImpawnLoan',InsurerImpawnLoan,\n" +
                "'FixedDeposit',FixedDeposit,\n" +
                "'RefundableDeposit',RefundableDeposit,\n" +
                "'RefCapDeposit',RefCapDeposit,\n" +
                "'IndepAccAssets',IndepAccAssets,\n" +
                "'OtherAssets',OtherAssets,\n" +
                "'AExceptionalItems',AExceptionalItems,\n" +
                "'AAdjustmentItems',AAdjustmentItems,\n" +
                "'TotalAssets',TotalAssets,\n" +
                "'ShortTermLoan',ShortTermLoan,\n" +
                "'ImpawnedLoan',ImpawnedLoan,\n" +
                "'NotAccountsPayable',NotAccountsPayable,\n" +
                "'NotesPayable',NotesPayable,\n" +
                "'AccountsPayable',AccountsPayable,\n" +
                "'STBondsPayable',STBondsPayable,\n" +
                "'AdvanceReceipts',AdvanceReceipts,\n" +
                "'SalariesPayable',SalariesPayable,\n" +
                "'OtherPayableED',OtherPayableED,\n" +
                "'OtherPayable',OtherPayable,\n" +
                "'DividendPayable',DividendPayable,\n" +
                "'InterestPayable',InterestPayable,\n" +
                "'TaxsPayable',TaxsPayable,\n" +
                "'ContractLiability',ContractLiability,\n" +
                "'AccruedExpense',AccruedExpense,\n" +
                "'DeferredProceeds',DeferredProceeds,\n" +
                "'HoldAndFSLi',HoldAndFSLi,\n" +
                "'NonCurLiaInOY',NonCurLiaInOY,\n" +
                "'OtherCurrentLiability',OtherCurrentLiability,\n" +
                "'CLExceptionalItems',CLExceptionalItems,\n" +
                "'CLAdjustmentItems',CLAdjustmentItems,\n" +
                "'TotalCurrentLiability',TotalCurrentLiability,\n" +
                "'LongTermLoan',LongTermLoan,\n" +
                "'BondsPayable',BondsPayable,\n" +
                "'LPreferStock',LPreferStock,\n" +
                "'LPerpetualDebt',LPerpetualDebt,\n" +
                "'LTAccountPayableTotal',LTAccountPayableTotal,\n" +
                "'LongtermAccountPayable',LongtermAccountPayable,\n" +
                "'SpecificAccountPayable',SpecificAccountPayable,\n" +
                "'LongSalariesPay',LongSalariesPay,\n" +
                "'EstimateLiability',EstimateLiability,\n" +
                "'DeferredTaxLiability',DeferredTaxLiability,\n" +
                "'LongDeferIncome',LongDeferIncome,\n" +
                "'OtherNonCurLia',OtherNonCurLia,\n" +
                "'NCLExceptionalItems',NCLExceptionalItems,\n" +
                "'NCLAdjustmentItems',NCLAdjustmentItems,\n" +
                "'TotalNonCurLia',TotalNonCurLia,\n" +
                "'BorFromCB',BorFromCB,\n" +
                "'AbsInterDeposits',AbsInterDeposits,\n" +
                "'Deposit',Deposit,\n" +
                "'DepositOfInterbank',DepositOfInterbank,\n" +
                "'BorrowingCapital',BorrowingCapital,\n" +
                "'DerivativeLiability',DerivativeLiability,\n" +
                "'SBbackSecuPros',SBbackSecuPros,\n" +
                "'ProxySecuProceeds',ProxySecuProceeds,\n" +
                "'SubIssueSecuProceeds',SubIssueSecuProceeds,\n" +
                "'DepositsReceived',DepositsReceived,\n" +
                "'AdvanceInsurance',AdvanceInsurance,\n" +
                "'CommissionPayable',CommissionPayable,\n" +
                "'ReinsurancePayables',ReinsurancePayables,\n" +
                "'CompensationPayable',CompensationPayable,\n" +
                "'PolicyDividendPayable',PolicyDividendPayable,\n" +
                "'InsDepInvest',InsDepInvest,\n" +
                "'LTInsContractReserve',LTInsContractReserve,\n" +
                "'UnearnedPremiumReserve',UnearnedPremiumReserve,\n" +
                "'OutingClaRes',OutingClaRes,\n" +
                "'LifeInsuranceReserve',LifeInsuranceReserve,\n" +
                "'LTHealthInsuranceLR',LTHealthInsuranceLR,\n" +
                "'IndependenceLiability',IndependenceLiability,\n" +
                "'OtherLiability',OtherLiability,\n" +
                "'LExceptionalItems',LExceptionalItems,\n" +
                "'LAdjustmentItems',LAdjustmentItems,\n" +
                "'TotalLiability',TotalLiability,\n" +
                "'PaidInCapital',PaidInCapital,\n" +
                "'OtherEquityinstruments',OtherEquityinstruments,\n" +
                "'EPreferStock',EPreferStock,\n" +
                "'EPerpetualDebt',EPerpetualDebt,\n" +
                "'CapitalReserveFund',CapitalReserveFund,\n" +
                "'TreasuryStock',TreasuryStock,\n" +
                "'SurplusReserveFund',SurplusReserveFund,\n" +
                "'RetainedProfit',RetainedProfit,\n" +
                "'OtherCompositeIncome',OtherCompositeIncome,\n" +
                "'OrdRiskResFund',OrdRiskResFund,\n" +
                "'TradeRiskRSRVFd',TradeRiskRSRVFd,\n" +
                "'ConversionMarginInFCR',ConversionMarginInFCR,\n" +
                "'UncInvLoss',UncInvLoss,\n" +
                "'OtherReserves',OtherReserves,\n" +
                "'SpecificReserves',SpecificReserves,\n" +
                "'SEExceptionalItems',SEExceptionalItems,\n" +
                "'SEAdjustmentItems',SEAdjustmentItems,\n" +
                "'SEWithoutMI',SEWithoutMI,\n" +
                "'MinorityInterests',MinorityInterests,\n" +
                "'OtherItemsEffectingSE',OtherItemsEffectingSE,\n" +
                "'TotalShareholderEquity',TotalShareholderEquity,\n" +
                "'LEExceptionalItems',LEExceptionalItems,\n" +
                "'LEAdjustmentItems',LEAdjustmentItems,\n" +
                "'TotalLiaAndEquity',TotalLiaAndEquity,\n" +
                "'ReceivablesFin',ReceivablesFin,\n" +
                "'Financing',Financing,\n" +
                "'FinLeaseReceivable',FinLeaseReceivable,\n" +
                "'UsufructAssets',UsufructAssets,\n" +
                "'InsContractReserve',InsContractReserve,\n" +
                "'LeaseLiabilities',LeaseLiabilities,\n" +
                "'FinLeasesPayable',FinLeasesPayable,\n" +
                "'DepositInAssociate',DepositInAssociate,\n" +
                "'DebtAssets',DebtAssets,\n" +
                "'DebitofAssociate',DebitofAssociate,\n" +
                "'Receivables',Receivables,\n" +
                "'CashDepositReceive',CashDepositReceive,\n" +
                "'PledgDepositReceive',PledgDepositReceive,\n" +
                "'SettlementReceive',SettlementReceive,\n" +
                "'RiskOfLossReceive',RiskOfLossReceive,\n" +
                "'FeeCommissionReceive',FeeCommissionReceive,\n" +
                "'FutureMemberInvestment',FutureMemberInvestment,\n" +
                "'FinancialInvestment',FinancialInvestment,\n" +
                "'ShortTermBondPay',ShortTermBondPay,\n" +
                "'TradingLiability',TradingLiability,\n" +
                "'AmongTradingLiability',AmongTradingLiability,\n" +
                "'AmongFinLiaAtFValTPL',AmongFinLiaAtFValTPL,\n" +
                "'CashDepositPay',CashDepositPay,\n" +
                "'PledgDepositPay',PledgDepositPay,\n" +
                "'FutureProtectFundPay',FutureProtectFundPay,\n" +
                "'GuarantCompensateRSRV',GuarantCompensateRSRV,\n" +
                "'GuaranteeReserve',GuaranteeReserve,\n" +
                "'FutureRiskReserve',FutureRiskReserve,\n" +
                "'Payables',Payables))) as report_json,\n" +
                "       '' as created_date,\n" +
                "       '' as update_date,\n" +
                "       a.EndDate,\n" +
                "       '' as report_encrypt_key,\n" +
                "       regexp_replace(current_date(),'-',''),\n" +
                "       '1' as isadd\n" +
                "FROM\n" +
                "  (select a.* from (select a.*,row_number ( ) over ( PARTITION BY CompanyCode,EndDate ORDER BY InfoPublDate desc,IfAdjusted) AS adjust from a_dc_ep_ods.LC_STIBBalanceSheet a  where IfMerged = 1 and IfComplete = 1 and InfoSourceCode in (110101,110102,110103,110104))a where a.adjust = 1)a\n" +
                "INNER JOIN\n" +
                "  (select * from a_dc_ep_ods.SecuMain where ListedState = 1 and ListedSector =7 and SecuCategory = 1) b on a.CompanyCode = b.CompanyCode"));
        insert_data.createOrReplaceTempView("tmp_t_ipo_company_balance_sheet1");
        spark.sql("insert overwrite table a_dc_ep_incr.t_ipo_company_balance_sheet select * from tmp_t_ipo_company_balance_sheet1");
        spark.stop();
    }
}
